You don't need the VALUES when you select ie:
INSERT INTO tbl (a,b) SELECT a,b FROM tbl2;
hth,
- Stuart
Stuart,
Thanks for the help on the syntax. My new problem is that now the function seems to be looping endlessly. No records are being inserted but when I check the 'album_album_id_seq' after stopping the function I see that it's value has been incremented to some really high number. I've tried stripping the function down to just the first INSERT statement with the same result. Does the fact that I don't have a WHERE clause in the FOR/SELECT make any difference?
Thanks for any input,
Dan
My new function:
CREATE FUNCTION catalog_batch() RETURNS text AS '
DECLARE
mp3rec RECORD;
BEGIN
FOR mp3rec IN SELECT * FROM mp3catalog LOOP
INSERT INTO album (title, media, path, release_date) VALUES (mp3catalog.album, mp3catalog.audioformat, mp3catalog.path, mp3catalog.year);
INSERT INTO track (album_id, trk_no, trk_title, time, genre, bitrate, channel, notes) SELECT currval("album_album_id_seq"), mp3catalog.track, mp3catalog.title, mp3catalog.time, mp3catalog.genre, mp3catalog.bitrate, mp3catalog.channels, mp3catalog.comment;
INSERT INTO participant (name) VALUES (mp3catalog.artist);
INSERT INTO performance (album_id, participant_id) SELECT currval("album_album_id_seq"), currval("participant_participant_id_seq");
END LOOP;
RETURN Complete;
END;'
LANGUAGE 'plpgsql';